import pandas as pd
import pycountry
from IPython.display import display
import numpy as np
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots
import plotly.io as pio
from collections import Counter
import os
from statsmodels.stats.proportion import proportions_ztest
from scipy import stats
import itertools
from sklearn import cluster, metrics, feature_extraction, decomposition
from wordcloud import WordCloud
from sentence_transformers import SentenceTransformer

# This allows for interactive maps when using jupyter nbconvert to create this HTML page
pio.renderers.default = "plotly_mimetype+notebook"
pd.options.plotting.backend = "plotly"

if os.path.isfile("linkedin_job_postings.parquet"):
    postings = pd.read_parquet("linkedin_job_postings.parquet")
else:
    postings = pd.read_csv("linkedin_job_postings.csv")
display(postings)

if os.path.isfile("job_skills.parquet"):
    skills = pd.read_parquet("job_skills.parquet")
else:
    skills = pd.read_csv("job_skills.csv")
display(skills)

if os.path.isfile("job_summary.parquet"):
    summary = pd.read_parquet("job_summary.parquet")
else:
    summary = pd.read_csv("job_summary.csv")
display(summary)

# https://github.com/dr5hn/countries-states-cities-database
cities = pd.read_csv("cities.csv")[
    ["name", "state_name", "country_code", "country_name", "latitude", "longitude"]
]

# Deletes .'s and replaces accented characters with standard alphabetical
cities["name"] = cities["name"].str.replace(".", "")
cities["name"] = (
    cities["name"]
    .str.normalize("NFKD")
    .str.encode("ascii", errors="ignore")
    .str.decode("utf-8")
)
print("Rows before dropping nulls and duplicates", cities.shape[0])
cities.drop_duplicates(["name", "state_name", "country_code"], inplace=True)
cities.dropna(inplace=True)
cities.reset_index(inplace=True, drop=True)
print("Rows after dropping nulls and duplicates", cities.shape[0])
print("Counts:")
print(cities.count())
display(cities[["name", "state_name", "country_code", "country_name"]].describe())
display(cities[["latitude", "longitude"]].describe())
display(cities)

Rows before dropping nulls and duplicates 150574
Rows after dropping nulls and duplicates 150497
Counts:
name            150497
state_name      150497
country_code    150497
country_name    150497
latitude        150497
longitude       150497
dtype: int64

df = postings.merge(skills, how="inner", on="job_link").merge(
    summary, how="inner", on="job_link"
)
print("Rows before dropping nulls and duplicates", df.shape[0])
df.drop_duplicates("job_link", inplace=True)
df.dropna(inplace=True)
df.reset_index(inplace=True, drop=True)
print("Rows after dropping nulls and duplicates", df.shape[0])

# We only want the alphabetical letters
df["job_skills"] = df["job_skills"].str.lower().replace("[^a-z]", " ", regex=True)

# We clean job_location to remote accented characters
df["job_location"] = (
    df["job_location"]
    .str.replace(".", "")
    .str.normalize("NFKD")
    .str.encode("ascii", errors="ignore")
    .str.decode("utf-8")
)

print("Counts:")
print(df.count())
display(df.describe())
display(df)

Rows before dropping nulls and duplicates 1296381
Rows after dropping nulls and duplicates 1294268
Counts:
job_link               1294268
last_processed_time    1294268
got_summary            1294268
got_ner                1294268
is_being_worked        1294268
job_title              1294268
company                1294268
job_location           1294268
first_seen             1294268
search_city            1294268
search_country         1294268
search_position        1294268
job_level              1294268
job_type               1294268
job_skills             1294268
job_summary            1294268
dtype: int64

del postings, summary, skills  # Clear memory

def t_f_to_bool(x):
    return True if x == "t" else False


def split_str(x):
    if isinstance(x, str):
        return x.split(", ")
    return []


job_level = {"Associate": 0, "Mid senior": 1}

# Turn t and f into True and False
for col in ["got_summary", "got_ner", "is_being_worked"]:
    df[col] = df[col].apply(t_f_to_bool)

# Convert strings to dates
df["last_processed_time"] = pd.to_datetime(
    df["last_processed_time"],
    format="ISO8601",
)
df["first_seen"] = pd.to_datetime(
    df["first_seen"],
    format="ISO8601",
)

df.head()

df["job_loc_list"] = df["job_location"].apply(split_str)

length_of_cities = df["job_loc_list"].map(len)
num_invalid = df["job_loc_list"][
    (length_of_cities > 3) | (length_of_cities < 1)
].shape[0]
num_invalid, round(num_invalid / df["job_loc_list"].shape[0] * 100, 2)

(28, 0.0)

us_states = {
    "AL": "Alabama",
    "AK": "Alaska",
    "AZ": "Arizona",
    "AR": "Arkansas",
    "CA": "California",
    "CO": "Colorado",
    "CT": "Connecticut",
    "DE": "Delaware",
    "FL": "Florida",
    "GA": "Georgia",
    "HI": "Hawaii",
    "ID": "Idaho",
    "IL": "Illinois",
    "IN": "Indiana",
    "IA": "Iowa",
    "KS": "Kansas",
    "KY": "Kentucky",
    "LA": "Louisiana",
    "ME": "Maine",
    "MD": "Maryland",
    "MA": "Massachusetts",
    "MI": "Michigan",
    "MN": "Minnesota",
    "MS": "Mississippi",
    "MO": "Missouri",
    "MT": "Montana",
    "NE": "Nebraska",
    "NV": "Nevada",
    "NH": "New Hampshire",
    "NJ": "New Jersey",
    "NM": "New Mexico",
    "NY": "New York",
    "NC": "North Carolina",
    "ND": "North Dakota",
    "OH": "Ohio",
    "OK": "Oklahoma",
    "OR": "Oregon",
    "PA": "Pennsylvania",
    "RI": "Rhode Island",
    "SC": "South Carolina",
    "SD": "South Dakota",
    "TN": "Tennessee",
    "TX": "Texas",
    "UT": "Utah",
    "VT": "Vermont",
    "VA": "Virginia",
    "WA": "Washington",
    "WV": "West Virginia",
    "WI": "Wisconsin",
    "WY": "Wyoming",
    "DC": "District of Columbia",
    "AS": "American Samoa",
    "GU": "Guam",
    "MP": "Northern Mariana Islands",
    "PR": "Puerto Rico",
    "UM": "United States Minor Outlying Islands",
    "VI": "US Virgin Islands",
}

us_states |= {v: k for k, v in us_states.items()}

# https://en.wikipedia.org/wiki/List_of_largest_cities_of_U.S._states_and_territories_by_population
state_cities = {
    "Alabama": "Huntsville",
    "Alaska": "Anchorage",
    "Arizona": "Phoenix",
    "Arkansas": "Little Rock",
    "California": "Los Angeles",
    "Colorado": "Denver",
    "Connecticut": "Bridgeport",
    "Delaware": "Wilmington",
    "Florida": "Jacksonville",
    "Georgia": "Atlanta",
    "Hawaii": "Honolulu",
    "Idaho": "Boise",
    "Illinois": "Chicago",
    "Indiana": "Indianapolis",
    "Iowa": "Des Monies",
    "Kansas": "Wichita",
    "Kentucky": "Louisville",
    "Louisiana": "New Orleans",
    "Maine": "Portland",
    "Maryland": "Baltimore",
    "Massachusetts": "Boston",
    "Michigan": "Detroit",
    "Minnesota": "Minneapolis",
    "Mississippi": "Jackson",
    "Missouri": "Kansas City",
    "Montana": "Billings",
    "Nebraska": "Omaha",
    "Nevada": "Las Vegas",
    "New Hampshire": "Manchester",
    "New Jersey": "Newark",
    "New Mexico": "Albuquerque",
    "New York": "New York City",
    "North Carolina": "Charlotte",
    "North Dakota": "Fargo",
    "Ohio": "Columbus",
    "Oklahoma": "Oklahoma City",
    "Oregon": "Portland",
    "Pennsylvania": "Philadelphia",
    "Rhode Island": "Providence",
    "South Carolina": "Charleston",
    "South Dakota": "Sioux Falls",
    "Tennessee": "Nashville",
    "Texas": "Houston",
    "Utah": "Salt Lake City",
    "Vermont": "Burlington",
    "Virginia": "Virginia Beach",
    "Washington": "Seattle",
    "West Virginia": "Charleston",
    "Wisconsin": "Milwaukee",
    "Wyoming": "Cheyenne",
    "District of Columbia": "Washington DC",
}

valid_cities = set(
    zip(
        cities["name"],
        cities["state_name"],
        cities["country_code"],
    )
)
valid_countries = set(cities["country_code"])
us_cities = dict(
    zip(
        cities[cities["country_code"] == "US"]["name"],
        cities[cities["country_code"] == "US"]["state_name"],
    )
)
inv = Counter()


def to_columns(loc):
    city = state = country = None
    if len(loc) == 1:
        item = loc[0].replace(" Metropolitan Area", "")
        if item == "United States" or "New York" in item:
            city = "New York City"
            state = "New York"
            country = "US"
        if item not in us_cities:
            item = item.replace("St ", "Saint ")
        for us_city, us_state in us_cities.items():
            if us_city in item:
                city = us_city
                state = us_state
                country = "US"

    if len(loc) == 2:
        """
        Formats:
        - city, state (U.S.)
        - United Kingdom, Great Britain (imputate city with London)
        - state, United States
        """

        temp_state = loc[1].replace(" Metropolitan Area", "")
        if temp_state in us_states:
            state = us_states[temp_state]
            city = loc[0].replace(" Metropolitan Area", "")
            country = "US"
            if city == state == "New York":
                city = "New York City"
            if city not in us_cities:
                city = city.replace("St ", "Saint ")
            if city not in us_cities:
                for us_city, us_state in us_cities.items():
                    if us_city in loc[0] and state == us_state:
                        city = us_city
        elif loc[1] == "United States" or loc[1] == "US":
            state = loc[0].replace(" Metropolitan Area", "")
            # Imputates with largest city in state
            city = state_cities[state]
            country = "US"
        elif loc[1] == "United Kingdom":
            state = loc[0].replace(" Metropolitan Area", "")
            # Imputate with London
            city = "London"
            country = "GB"

        if city == "Washington" and state == "District of Columbia":
            city = "Washington DC"

    elif len(loc) == 3:
        """
        Formats:
        city, state, country
        """

        # Full country name to alpha 2 code. Ex: United States -> US
        country_obj = pycountry.countries.get(name=loc[2])
        if country_obj is not None:
            country = country_obj.alpha_2
        else:
            if loc[2] == "Taiwan":
                country = "TW"
            elif loc[2] == "Turkey":
                country = "TR"
            elif loc[2] == "Tanzania":
                country = "TZ"

        city = loc[0].replace(" Metropolitan Area", "")

        # Code to full name. Ex: AL -> Alabama
        if country == "US" and loc[1] in us_states.keys() and len(loc[1]) == 2:
            state = us_states[loc[1]]
        else:
            state = loc[1].replace(" Metropolitan Area", "")

    if (city, state, country) not in valid_cities:
        inv[", ".join(loc) + f", Split: {city}, {state}, {country}"] += 1
    return [city, state, country]


df[["city", "state", "country"]] = pd.DataFrame(
    list(df["job_loc_list"].map(to_columns))
)

# Prints the occurences of the invalid cities (ones that aren't in the cities dataset)
# This is either because of formatting errors, or it's a small city that's not in the dataset
len(inv), sorted(inv.items(), key=lambda x: -x[1])[:10]

(11173,
 [('West Midlands, England, United Kingdom, Split: West Midlands, England, GB',
   806),
  ('West Yorkshire, England, United Kingdom, Split: West Yorkshire, England, GB',
   411),
  ('Austin, Texas Metropolitan Area, Split: Austin, TX, US', 356),
  ('Charleston, South Carolina Metropolitan Area, Split: Charleston, SC, US',
   354),
  ('Brick, NJ, Split: Brick, New Jersey, US', 351),
  ('Little London, England, United Kingdom, Split: Little London, England, GB',
   346),
  ('University Park, PA, Split: University Park, Pennsylvania, US', 329),
  ('Hull, England, United Kingdom, Split: Hull, England, GB', 327),
  ('West Chester, OH, Split: West Chester, Ohio, US', 320),
  ('Scotland, United Kingdom, Split: London, Scotland, GB', 318)])

del valid_cities  # Clear memory

df = df.merge(
    cities,
    how="left",
    left_on=["city", "state", "country"],
    right_on=["name", "state_name", "country_code"],
)
df

# Invalid merge rows
m = df[df["latitude"].isnull()]
print(m["country"].value_counts())
m

country
US    41903
GB    15871
CA     5395
AU     2335
MX      644
FR       29
MY       25
AE       10
IN       10
SA        9
PH        6
AR        5
ES        5
IT        5
BR        4
GR        4
IE        4
BE        3
CN        3
CH        3
HU        3
TR        3
SE        2
DK        2
CM        2
EG        1
NG        1
DE        1
ID        1
TZ        1
JP        1
CZ        1
PL        1
TW        1
CL        1
Name: count, dtype: int64

df = df.dropna()
df.reset_index(inplace=True, drop=True)

print(
    "Percent of original dataset retained:", round(100 * df.shape[0] / 1300000, 3)
)

Percent of original dataset retained: 94.197

map_df = df.groupby(["country", "country_name"]).size()
map_df = pd.DataFrame(map_df).reset_index()
map_df.rename(
    {"country": "alpha_2", "country_name": "country", 0: "job count"},
    inplace=True,
    axis=1,
)
map_df["Log10(job count)"] = np.log10(map_df["job count"])
map_df["alpha_3"] = map_df["alpha_2"].map(
    lambda x: pycountry.countries.get(alpha_2=x).alpha_3
)
map_df

fig = px.choropleth(
    map_df,
    locations="alpha_3",
    hover_name="country",
    color="Log10(job count)",
    hover_data=["job count"],
    projection="orthographic",
    color_continuous_scale="viridis",
)
fig.update_layout(
    margin=dict(l=0, r=0, t=0, b=0, pad=4, autoexpand=True),
    width=600,
)
fig.update_coloraxes(colorbar_len=0.6)
fig.show()

latlon = df.groupby(["latitude", "longitude", "city", "state", "country"]).size()
latlon = pd.DataFrame(latlon).reset_index().rename({0: "count"}, axis=1)
fig = px.scatter_geo(
    latlon.query("count > 100"),
    lat="latitude",
    lon="longitude",
    size="count",
    color="count",
    color_continuous_scale="viridis",
    hover_data=["city", "state", "country"],
    opacity=1,
    projection="orthographic",
)
fig.update_layout(
    margin=dict(l=0, r=0, t=0, b=0, pad=4, autoexpand=True),
    width=600,
)
fig.update_coloraxes(colorbar_len=0.6)
fig.show()
del latlon  # Clear memory

top3 = map_df.sort_values("job count", ascending=False).iloc[:3][
    ["country", "job count"]
]
display(top3)
top3 = set(top3["country"].values)

num_mid_high = df.query("country_name in @top3").copy()
num_mid_high["job_level"] = num_mid_high["job_level"].apply(
    lambda x: job_level[x]
)  # Mid-Senior == 1, Associate == 0
num_mid_high = num_mid_high.groupby("country_name")["job_level"].sum().sort_values()
num_jobs = (
    df.query("country_name in @top3")
    .groupby("country_name")["job_level"]
    .count()
    .sort_values()
)
props = num_mid_high / num_jobs
fig = props.plot(kind="bar", backend="plotly")
fig.update_layout(
    yaxis_range=[0, 1],
    title="Sample Proportion of Mid-Senior Jobs by (Top 3) Countries",
)

contingency_table = pd.crosstab(
    df.query("country_name in @top3")["country_name"],
    df.query("country_name in @top3")["job_level"],
)
display(contingency_table)
display((contingency_table.T / contingency_table.sum(axis=1)).T)
display(
    contingency_table.plot(
        kind="bar", title="Frequency", backend="plotly", barmode="group"
    )
)
display(
    ((contingency_table.T / contingency_table.sum(axis=1)).T).plot(
        kind="bar", title="Probability distributions", backend="plotly", barmode="group"
    )
)
stats.chi2_contingency(contingency_table)

Chi2ContingencyResult(statistic=395.8733435193279, pvalue=1.0894208642323106e-86, dof=2, expected_freq=array([[  4993.54309072,  40958.45690928],
       [  9965.78712056,  81742.21287944],
       [115357.66978871, 946196.33021129]]))

print("USA > Canada")
print(
    proportions_ztest(
        num_mid_high[["United States", "Canada"]],
        num_jobs[["United States", "Canada"]],
        alternative="larger",
    )
)

print("UK > USA")
print(
    proportions_ztest(
        num_mid_high[["United Kingdom", "United States"]],
        num_jobs[["United Kingdom", "United States"]],
        alternative="larger",
    )
)

print("UK > Canada")
print(
    proportions_ztest(
        num_mid_high[["United Kingdom", "Canada"]],
        num_jobs[["United Kingdom", "Canada"]],
        alternative="larger",
    )
)

USA > Canada
(1.0005819489094683, 0.1585144803056488)
UK > USA
(19.790144761015508, 1.8100028997368962e-87)
UK > Canada
(13.432934673059961, 1.9386787397135185e-41)

contingency_table = contingency_table = pd.crosstab(
    df.query("country_name in @top3")["country_name"],
    df.query("country_name in @top3")["job_type"],
)
display(contingency_table)
display((contingency_table.T / contingency_table.sum(axis=1)).T)
display(
    contingency_table.plot(
        kind="bar", title="Frequency", backend="plotly", barmode="group"
    )
)
display(
    ((contingency_table.T / contingency_table.sum(axis=1)).T).plot(
        kind="bar", title="Probability distributions", backend="plotly", barmode="group"
    )
)
stats.chi2_contingency(contingency_table)

Chi2ContingencyResult(statistic=330.6899939082778, pvalue=2.585770377761359e-70, dof=4, expected_freq=array([[2.30255457e+02, 4.55702718e+04, 1.51472761e+02],
       [4.59528801e+02, 9.09461718e+04, 3.02299443e+02],
       [5.31921574e+03, 1.05273556e+06, 3.49922780e+03]]))

company_counts = (
    df.query("country_name in @top3")
    .groupby("country_name")["company"]
    .value_counts()
    .groupby("country_name")
    .head(50)
    .reset_index()
)
company_counts

fig = make_subplots(3, 1)

for i, country in enumerate(["United States", "Canada", "United Kingdom"]):
    fig.add_trace(
        go.Bar(
            text=company_counts.query("country_name == @country")["company"],
            y=company_counts.query("country_name == @country")["count"],
            name=country,
        ),
        row=i + 1,
        col=1,
    )
fig.update_layout(
    height=2000, title="Top 50 Company Job Distribution for each of the Top 3 Countries"
)
fig.update_xaxes(title="Nth Largest Company")
fig.update_yaxes(title="Number of Jobs")
fig

# Gets samples from each of the countries
pmfs = {}
for country in top3:
    data = []
    counts = company_counts.query("country_name == @country")["count"].reset_index()[
        "count"
    ]
    for i, x in enumerate(counts):
        data += [i] * x
    pmfs[country] = data
    sum_stats = pd.DataFrame({country: data}).describe()
    sum_stats.loc["skew"] = stats.skew(data)
    display(sum_stats)

for c1, c2 in itertools.combinations(top3, 2):
    print("Comparing distributions of:", c1, ",", c2)
    print(stats.ks_2samp(pmfs[c1], pmfs[c2]))

Comparing distributions of: United States , Canada
KstestResult(statistic=0.061611845347954136, pvalue=3.9712986155353705e-44, statistic_location=2, statistic_sign=1)
Comparing distributions of: United States , United Kingdom
KstestResult(statistic=0.054722840970027664, pvalue=9.298692564618634e-56, statistic_location=3, statistic_sign=1)
Comparing distributions of: Canada , United Kingdom
KstestResult(statistic=0.05345704181311936, pvalue=4.628346419662773e-22, statistic_location=0, statistic_sign=-1)

transformer = SentenceTransformer("all-MiniLM-L6-v2", cache_folder=".")

if os.path.isfile("skills_encodings.npz"):
    encodings = np.load("skills_encodings.npz")["skills"]
else:
    encodings = transformer.encode(df.job_skills)
    np.savez_compressed("skills_encodings.npz", skills=encodings)

print(encodings.shape)

(1224559, 384)

del cities, length_of_cities, m

silhouette = []
wcss = []
clusters = [2, 3, 4, 5, 7, 9, 11, 13, 15]
for i in clusters:
    model = cluster.KMeans(
        n_clusters=i, max_iter=100, init="k-means++", random_state=123
    ).fit(encodings)
    silhouette.append(
        metrics.silhouette_score(encodings, model.labels_, sample_size=1000)
    )
    wcss.append(model.inertia_)
    print(f"{i} clusters done")

silhouette_data = pd.DataFrame(
    {"Number of Clusters": clusters, "Silhouette Coefficient": silhouette}
)
wcss_data = pd.DataFrame({"Number of Clusters": clusters, "WCSS": wcss})
px.line(silhouette_data, x="Number of Clusters", y="Silhouette Coefficient").show()
px.line(wcss_data, x="Number of Clusters", y="WCSS").show()

2 clusters done
3 clusters done
4 clusters done
5 clusters done
7 clusters done
9 clusters done
11 clusters done
13 clusters done
15 clusters done

num_clusters = 7
labels = cluster.KMeans(
    n_clusters=num_clusters,
    max_iter=100,
    init="k-means++",
    random_state=123,
).fit_predict(encodings)

clustered_sentences = [[] for _ in range(num_clusters)]
for i in range(len(labels)):
    clustered_sentences[labels[i]].append(df.job_skills[i])

from collections import Counter

stop_words = [
    "to",
    "in",
    "and",
    "of",
    "a",
    "s",
    "skills",
    "management",
    "with",
]


for i in range(num_clusters):
    
    word_counter = Counter(" ".join(clustered_sentences[i]).split(" "))
    for stop_word in stop_words:
        if stop_word in word_counter:
            del word_counter[stop_word]
    fig = px.imshow(WordCloud(width=1920, height=1080, random_state=123).generate_from_frequencies(word_counter))
    fig.update_xaxes(showticklabels=False).update_yaxes(showticklabels=False)
    fig.update_layout(margin=dict(l=0, r=0, t=0, b=0)).show()

clusters = {
    0: "Therapist / Counselor",
    1: "Business Analytics",
    2: "General Inventory / Restaurant Jobs",
    3: "Computer Science Jobs",
    4: "Medical Jobs",
    5: "Business Management",
    6: "Retail jobs",
}

df["labels"] = labels.copy()

C:\Users\Vikram\AppData\Local\Temp\ipykernel_4580\3709420813.py:1: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

df.query("labels == 3")["job_title"][:20]

19                        Test Conductor - Space Systems
34          Sr Experience Design Manager, Learn and Help
47                                            Designer I
51     CIBC | Analyste Principal, Intégration des cli...
84              Senior Network Engineering Lead - Remote
98                      Lead Software Engineer, Back End
99               Founding Fullstack Engineer, AI Startup
101                              Design Strategy Manager
131                                  RESEARCH ANALYST II
141                                     Android Engineer
170                         Ad Operations Manager (Hulu)
171                     Senior Software Engineer, DevOps
173                     Senior Machine Learning Engineer
198    Contracts Lifecycle Management (CLM) Solutions...
211                         Senior Specialist Programmer
225                   IT / Business Framework Specialist
242                                        R&D Scientist
243                     Distinguished Applied Researcher
270                             Senior Flutter Developer
278         Principal Software Engineer, ML Accelerators
Name: job_title, dtype: object

del encodings, labels, word_counter

cv = feature_extraction.text.CountVectorizer(analyzer="word", ngram_range=(2, 2), min_df=0.01, max_df=0.5)

encodings = cv.fit_transform(df["job_skills"])
encodings.shape

(1224559, 269)

labels = decomposition.LatentDirichletAllocation(n_components=7, random_state=123, n_jobs=14).fit_transform(encodings)
labels.shape

(1224559, 7)

clustered_sentences = [[] for _ in range(num_clusters)]
for i in range(len(labels)):
    clustered_sentences[labels[i].argmax()].append(df.job_skills[i])

from collections import Counter

# Exclude these words
stop_words = [
    "to",
    "in",
    "and",
    "of",
    "a",
    "s",
    "skills",
    "management",
    "with",
]


for i in range(num_clusters):
    
    word_counter = Counter(" ".join(clustered_sentences[i]).split(" "))
    for stop_word in stop_words:
        if stop_word in word_counter:
            del word_counter[stop_word]
    fig = px.imshow(WordCloud(width=1920, height=1080, random_state=123).generate_from_frequencies(word_counter))
    fig.update_xaxes(showticklabels=False).update_yaxes(showticklabels=False)
    fig.update_layout(margin=dict(l=0, r=0, t=0, b=0)).show()

del encodings, labels, word_counter  # Clear memory again

fig = df["labels"].value_counts().rename(index=clusters).plot.bar()
fig.update_layout(
    title="Count of Jobs per Cluster", xaxis_title="Clusters", yaxis_title="Count"
)

fig = (
    df.query("labels == 3 and country_name in @top3")["country_name"]
    .value_counts()
    .plot.bar()
)
fig.update_layout(
    title="CS Jobs by Country", xaxis_title="Top 3 Countries", yaxis_title="Count"
)

contingency_table = pd.crosstab(
    df.query("country_name in @top3")["country_name"],
    df.query("country_name in @top3")["labels"] == 3,
)
display(contingency_table)
display((contingency_table.T / contingency_table.sum(axis=1)).T)
display(
    contingency_table.plot(
        kind="bar", title="Frequency", backend="plotly", barmode="group"
    )
)
display(
    ((contingency_table.T / contingency_table.sum(axis=1)).T).plot(
        kind="bar", title="Probability distributions", backend="plotly", barmode="group"
    )
)
stats.chi2_contingency(contingency_table)

Chi2ContingencyResult(statistic=230.07197381576603, pvalue=1.0977556110093208e-50, dof=2, expected_freq=array([[ 42501.57848057,   3450.42151943],
       [ 84821.87411421,   6886.12588579],
       [981844.54740522,  79709.45259478]]))

	job_link	last_processed_time	got_summary	got_ner	is_being_worked	job_title	company	job_location	first_seen	search_city	search_country	search_position	job_level	job_type
0	https://www.linkedin.com/jobs/view/account-exe...	2024-01-21 07:12:29.00256+00	t	t	f	Account Executive - Dispensing (NorCal/Norther...	BD	San Diego, CA	2024-01-15	Coronado	United States	Color Maker	Mid senior	Onsite
1	https://www.linkedin.com/jobs/view/registered-...	2024-01-21 07:39:58.88137+00	t	t	f	Registered Nurse - RN Care Manager	Trinity Health MI	Norton Shores, MI	2024-01-14	Grand Haven	United States	Director Nursing Service	Mid senior	Onsite
2	https://www.linkedin.com/jobs/view/restaurant-...	2024-01-21 07:40:00.251126+00	t	t	f	RESTAURANT SUPERVISOR - THE FORKLIFT	Wasatch Adaptive Sports	Sandy, UT	2024-01-14	Tooele	United States	Stand-In	Mid senior	Onsite
3	https://www.linkedin.com/jobs/view/independent...	2024-01-21 07:40:00.308133+00	t	t	f	Independent Real Estate Agent	Howard Hanna \| Rand Realty	Englewood Cliffs, NJ	2024-01-16	Pinehurst	United States	Real-Estate Clerk	Mid senior	Onsite
4	https://www.linkedin.com/jobs/view/group-unit-...	2024-01-19 09:45:09.215838+00	f	f	f	Group/Unit Supervisor (Systems Support Manager...	IRS, Office of Chief Counsel	Chamblee, GA	2024-01-17	Gadsden	United States	Supervisor Travel-Information Center	Mid senior	Onsite
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
1348449	https://www.linkedin.com/jobs/view/registered-...	2024-01-20 15:21:07.786118+00	t	t	f	Registered Nurse (RN) #CP-RN-7998660 - 2411627...	TravelNurseSource	Providence, RI	2024-01-14	Fall River	United States	Nurse Supervisor	Mid senior	Onsite
1348450	https://www.linkedin.com/jobs/view/constructio...	2024-01-20 15:21:10.885264+00	t	t	f	Construction Superintendent	Jobot	New Iberia, LA	2024-01-15	Lafayette	United States	Assistant Construction Superintendent	Mid senior	Onsite
1348451	https://www.linkedin.com/jobs/view/executive-c...	2024-01-21 07:40:00.304641+00	t	t	f	Executive Chef, Operations Support	NEXDINE Hospitality	Riverhead, NY	2024-01-14	Eastport	United States	Chef	Mid senior	Onsite
1348452	https://www.linkedin.com/jobs/view/rn-register...	2024-01-21 00:38:39.816821+00	t	t	f	RN- Registered Nurse, Analyst - - 23934913EXPP...	TravelNurseSource	Aurora, CO	2024-01-16	Colorado	United States	Occupational Analyst	Mid senior	Onsite
1348453	https://www.linkedin.com/jobs/view/on-demand-g...	2024-01-21 00:38:44.231492+00	t	t	f	On-Demand: Guest Advocate (Cashier), General M...	Target	Culver City, CA	2024-01-12	Malibu	United States	Cashier Ii	Mid senior	Onsite

	job_link	job_skills
0	https://www.linkedin.com/jobs/view/housekeeper...	Building Custodial Services, Cleaning, Janitor...
1	https://www.linkedin.com/jobs/view/assistant-g...	Customer service, Restaurant management, Food ...
2	https://www.linkedin.com/jobs/view/school-base...	Applied Behavior Analysis (ABA), Data analysis...
3	https://www.linkedin.com/jobs/view/electrical-...	Electrical Engineering, Project Controls, Sche...
4	https://www.linkedin.com/jobs/view/electrical-...	Electrical Assembly, Point to point wiring, St...
...	...	...
1296376	https://www.linkedin.com/jobs/view/community-a...	Communication Skills, Time Management, Custome...
1296377	https://www.linkedin.com/jobs/view/sr-it-analy...	Windows SQL, EDI X12, Edifecs Platform, Health...
1296378	https://www.linkedin.com/jobs/view/operations-...	Adaptability, Communication, Digital Fluency, ...
1296379	https://www.linkedin.com/jobs/view/float-patie...	CNA, EMT, BLS, Medical Assistant, CPCT, LPN, R...
1296380	https://www.linkedin.com/jobs/view/conductor-e...	Customer Service, Driving, Loading, Unloading,...

	job_link	job_summary
0	https://www.linkedin.com/jobs/view/restaurant-...	Rock N Roll Sushi is hiring a Restaurant Manag...
1	https://www.linkedin.com/jobs/view/med-surg-re...	Schedule\n: PRN is required minimum 12 hours p...
2	https://www.linkedin.com/jobs/view/registered-...	Description\nIntroduction\nAre you looking for...
3	https://uk.linkedin.com/jobs/view/commercial-a...	Commercial account executive\nSheffield\nFull ...
4	https://www.linkedin.com/jobs/view/store-manag...	Address:\nUSA-CT-Newington-44 Fenn Road\nStore...
...	...	...
1297327	https://www.linkedin.com/jobs/view/roofing-sup...	We are currently seeking experienced commercia...
1297328	https://www.linkedin.com/jobs/view/service-cen...	Overview\nStable and growing organization\nCom...
1297329	https://www.linkedin.com/jobs/view/flight-qual...	Rôle et responsabilités\nJob Description:\nFli...
1297330	https://www.linkedin.com/jobs/view/global-sour...	Job Description\nAre You Ready to Make It Happ...
1297331	https://www.linkedin.com/jobs/view/research-as...	Current Employees:\nIf you are a current Staff...

	latitude	longitude
count	150497.000000	150497.000000
mean	31.565417	2.328172
std	22.795936	68.026230
min	-75.000000	-179.121980
25%	18.993450	-58.963770
50%	40.680660	8.657070
75%	47.238680	27.732170
max	73.508190	179.466000

	name	state_name	country_code	country_name	latitude	longitude
0	Ashkasham	Badakhshan	AF	Afghanistan	36.68333	71.53333
1	Fayzabad	Badakhshan	AF	Afghanistan	37.11664	70.58002
2	Jurm	Badakhshan	AF	Afghanistan	36.86477	70.83421
3	Khandud	Badakhshan	AF	Afghanistan	36.95127	72.31800
4	Raghistan	Badakhshan	AF	Afghanistan	37.66079	70.67346
...	...	...	...	...	...	...
150492	Redcliff	Midlands Province	ZW	Zimbabwe	-19.03333	29.78333
150493	Shangani	Midlands Province	ZW	Zimbabwe	-19.78333	29.36667
150494	Shurugwi	Midlands Province	ZW	Zimbabwe	-19.67016	30.00589
150495	Shurugwi District	Midlands Province	ZW	Zimbabwe	-19.75000	30.16667
150496	Zvishavane District	Midlands Province	ZW	Zimbabwe	-20.30345	30.07514

Analysis of LinkedIn Jobs and Skills (2024)¶

1. Introduction¶

2. Data Curation¶

3. Exploratory Data Analysis (EDA)¶

3a. Data Preprocessing¶

3b. Basic Data Exploration and Summary Statistics¶

Conclusions of Exploratory Data Analysis¶

4. Primary Analysis¶

5. Visualization¶

6. Insights and Conclusions¶

Final Conclusions¶

	name	state_name	country_code	country_name
count	150497	150497	150497	150497
unique	131214	3487	197	197
top	Merkez	England	US	United States
freq	51	2918	19820	19820

	job_link	last_processed_time	got_summary	got_ner	is_being_worked	job_title	company	job_location	first_seen	search_city	search_country	search_position	job_level	job_type	job_skills	job_summary
count	1294268	1294268	1294268	1294268	1294268	1294268	1294268	1294268	1294268	1294268	1294268	1294268	1294268	1294268	1294268	1294268
unique	1294268	720667	1	1	1	564775	88932	28773	6	1018	4	1922	2	3	1285967	955934
top	https://www.linkedin.com/jobs/view/account-exe...	2024-01-19 09:45:09.215838+00	t	t	f	LEAD SALES ASSOCIATE-FT	Health eCareers	New York, NY	2024-01-14	North Carolina	United States	Account Executive	Mid senior	Onsite	front counter drivethru outside order taker ...	Dollar General Corporation has been delivering...
freq	1	573435	1294268	1294268	1294268	7315	40010	12561	458587	9485	1103592	19463	1153364	1283454	183	4565

	job_link	last_processed_time	got_summary	got_ner	is_being_worked	job_title	company	job_location	first_seen	search_city	search_country	search_position	job_level	job_type	job_skills	job_summary
0	https://www.linkedin.com/jobs/view/account-exe...	2024-01-21 07:12:29.002560+00:00	True	True	False	Account Executive - Dispensing (NorCal/Norther...	BD	San Diego, CA	2024-01-15	Coronado	United States	Color Maker	Mid senior	Onsite	medical equipment sales key competitors term...	Responsibilities\nJob Description Summary\nJob...
1	https://www.linkedin.com/jobs/view/registered-...	2024-01-21 07:39:58.881370+00:00	True	True	False	Registered Nurse - RN Care Manager	Trinity Health MI	Norton Shores, MI	2024-01-14	Grand Haven	United States	Director Nursing Service	Mid senior	Onsite	nursing bachelor of science in nursing maste...	Employment Type:\nFull time\nShift:\nDescripti...
2	https://www.linkedin.com/jobs/view/restaurant-...	2024-01-21 07:40:00.251126+00:00	True	True	False	RESTAURANT SUPERVISOR - THE FORKLIFT	Wasatch Adaptive Sports	Sandy, UT	2024-01-14	Tooele	United States	Stand-In	Mid senior	Onsite	restaurant operations management inventory ma...	Job Details\nDescription\nWhat You'll Do\nAs a...
3	https://www.linkedin.com/jobs/view/independent...	2024-01-21 07:40:00.308133+00:00	True	True	False	Independent Real Estate Agent	Howard Hanna \| Rand Realty	Englewood Cliffs, NJ	2024-01-16	Pinehurst	United States	Real-Estate Clerk	Mid senior	Onsite	real estate customer service sales negotiat...	Who We Are\nRand Realty is a family-owned brok...
4	https://www.linkedin.com/jobs/view/registered-...	2024-01-21 08:08:19.663033+00:00	True	True	False	Registered Nurse (RN)	Trinity Health MI	Muskegon, MI	2024-01-14	Muskegon	United States	Nurse Practitioner	Mid senior	Onsite	nursing bsn medical license virtual rn nur...	Employment Type:\nFull time\nShift:\n12 Hour N...

	job_link	last_processed_time	got_summary	got_ner	is_being_worked	job_title	company	job_location	first_seen	search_city	...	job_loc_list	city	state	country	name	state_name	country_code	country_name	latitude	longitude
8	https://www.linkedin.com/jobs/view/special-age...	2024-01-21 08:08:21.308995+00:00	True	True	False	Special Agent: Law/Legal Background	Federal Bureau of Investigation (FBI)	Austin, Texas Metropolitan Area	2024-01-16	Austin	...	[Austin, Texas Metropolitan Area]	Austin	TX	US	NaN	NaN	NaN	NaN	NaN	NaN
17	https://www.linkedin.com/jobs/view/analyst-ass...	2024-01-21 02:01:15.820041+00:00	True	True	False	Analyst, Associate Plan Configuration & Qualit...	RITE AID	Yocumtown, PA	2024-01-13	Lebanon	...	[Yocumtown, PA]	Yocumtown	Pennsylvania	US	NaN	NaN	NaN	NaN	NaN	NaN
25	https://uk.linkedin.com/jobs/view/property-man...	2024-01-21 06:01:13.568960+00:00	True	True	False	Property Manager	Knight Dragon Ltd	Royal Borough of Greenwich, England, United Ki...	2024-01-14	Basildon	...	[Royal Borough of Greenwich, England, United K...	Royal Borough of Greenwich	England	GB	NaN	NaN	NaN	NaN	NaN	NaN
59	https://uk.linkedin.com/jobs/view/ct-technolog...	2024-01-21 06:01:14.166308+00:00	True	True	False	CT Technologist	Host Healthcare, Inc.	Elsworth, England, United Kingdom	2024-01-16	Peterborough	...	[Elsworth, England, United Kingdom]	Elsworth	England	GB	NaN	NaN	NaN	NaN	NaN	NaN
104	https://uk.linkedin.com/jobs/view/corporate-re...	2024-01-21 07:43:54.617770+00:00	True	True	False	Corporate Real Estate Manager (f/m/div)	Siemens Gamesa	Hull, England, United Kingdom	2024-01-17	Kingston upon Hull	...	[Hull, England, United Kingdom]	Hull	England	GB	NaN	NaN	NaN	NaN	NaN	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
1294185	https://www.linkedin.com/jobs/view/mascot-at-w...	2024-01-20 05:13:01.735342+00:00	True	True	False	Mascot	Westgate Resorts	River Ranch, FL	2024-01-14	Florida	...	[River Ranch, FL]	River Ranch	Florida	US	NaN	NaN	NaN	NaN	NaN	NaN
1294200	https://www.linkedin.com/jobs/view/fleet-maint...	2024-01-20 05:13:08.025382+00:00	True	True	False	Fleet Maintenance Manager - Year Round in Echo...	Energy Jobline	Echo, UT	2024-01-17	Bountiful	...	[Echo, UT]	Echo	Utah	US	NaN	NaN	NaN	NaN	NaN	NaN
1294221	https://www.linkedin.com/jobs/view/scientist-i...	2024-01-21 08:28:03.629178+00:00	True	True	False	Scientist IV - Clearance Preferred	LMI	Fort Detrick, MD	2024-01-14	Fairfield	...	[Fort Detrick, MD]	Fort Detrick	Maryland	US	NaN	NaN	NaN	NaN	NaN	NaN
1294231	https://ca.linkedin.com/jobs/view/academic-tut...	2024-01-20 13:46:05.388491+00:00	True	True	False	Academic tutoring Private Tutoring Jobs Catalone	ClickJobs.io	Catalone, Nova Scotia, Canada	2024-01-16	Nova Scotia	...	[Catalone, Nova Scotia, Canada]	Catalone	Nova Scotia	CA	NaN	NaN	NaN	NaN	NaN	NaN
1294245	https://www.linkedin.com/jobs/view/ft-center-s...	2024-01-20 13:46:10.289403+00:00	True	True	False	FT Center Store Manager (H)	Food Lion	Powhatan, VA	2024-01-14	Abingdon	...	[Powhatan, VA]	Powhatan	Virginia	US	NaN	NaN	NaN	NaN	NaN	NaN

job_level	Associate	Mid senior
country_name
Canada	5134	40818
United Kingdom	8166	83542
United States	117017	944537

job_level	Associate	Mid senior
country_name
Canada	0.111725	0.888275
United Kingdom	0.089043	0.910957
United States	0.110232	0.889768

job_type	Hybrid	Onsite	Remote
country_name
Canada	326	45515	111
United Kingdom	774	90723	211
United States	4909	1053014	3631

job_type	Hybrid	Onsite	Remote
country_name
Canada	0.007094	0.990490	0.002416
United Kingdom	0.008440	0.989259	0.002301
United States	0.004624	0.991955	0.003420

	country_name	company	count
0	Canada	CareerBeacon	1355
1	Canada	ClickJobs.io	1197
2	Canada	Canadian Armed Forces \| Forces armées canadiennes	812
3	Canada	Stantec	719
4	Canada	Vancouver Coastal Health	663
...	...	...	...
145	United States	WIS International	1839
146	United States	The Home Depot	1789
147	United States	Northrop Grumman	1746
148	United States	Trinity Health	1744
149	United States	Ross Stores, Inc.	1734

	United States
count	259497.000000
mean	13.188993
std	13.685892
min	0.000000
25%	2.000000
50%	8.000000
75%	22.000000
max	49.000000
skew	0.987267

	Canada
count	13950.000000
mean	14.430323
std	13.962532
min	0.000000
25%	3.000000
50%	10.000000
75%	24.000000
max	49.000000
skew	0.891284

	United Kingdom
count	23149.000000
mean	14.552162
std	14.149398
min	0.000000
25%	2.000000
50%	10.000000
75%	24.000000
max	49.000000
skew	0.848315

labels	False	True
country_name
Canada	41674	4278
United Kingdom	85089	6619
United States	982405	79149

labels	False	True
country_name
Canada	0.906903	0.093097
United Kingdom	0.927825	0.072175
United States	0.925440	0.074560